﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class newwordStatistics : CWebBase
{
    public class SearchValue
    {
        public string searchLevel1 = "";
        public string searchLevel2 = "";
        public string searchLevel3 = "";

        public string wordCount1 = "";
        public string wordCount2 = "";
        public string wordCount3 = "";
        public string wordCount4 = "";

        public string searchWord = "";
    }

    public CDatabase _db = new CDatabase();
    public SearchValue sv = new SearchValue();

    // 총 갯수.
    public int _nwTotalCount, _nnwTotalCount = 0;

    public Hashtable nwStatis = new Hashtable();
    public Hashtable nnwStatis = new Hashtable();

    public Boolean isExcelType = false;

    private string where, dWhere;

    protected void Page_Load(object sender, EventArgs e)
    {

        _db.GetConnection();

        isExcelType = (CheckNull(Request["excelType"]) == "xls")? true : false;

        if (isExcelType)
        {
            string filename = DateTime.Now.ToString("yyyy-MM-dd_HHmmss") + ".xls";

            Response.ContentType = "application/vnd.ms-excel";
            Response.ContentType = "application/x-msexcel";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
            Response.Write("<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />");
            Response.Write(Request.Form["excel_contents"].Replace("<table ", "<table border=1 "));

            Response.End();
            return;
        }

        // 검색
        sv.searchLevel1 = CheckNull(Request["searchLevel1"], "0");
        sv.searchLevel2 = CheckNull(Request["searchLevel2"], "");
        sv.searchLevel3 = CheckNull(Request["searchLevel3"], "");

        sv.wordCount1 = CheckNull(Request["wordCount1"], "");
        sv.wordCount2 = CheckNull(Request["wordCount2"], "");
        sv.wordCount3 = CheckNull(Request["wordCount3"], "");
        sv.wordCount4 = CheckNull(Request["wordCount4"], "");

        // 검색 기본
        makeBaseWhere();

        string selectData = " Select count(Word) as WordCount, WordLevel ";

        // sql
        string listSql = "Select "+
                         "  wd.Word, wd.WordClass, wd.WordMean,  lv.LevelId, lv.TopLevelId, lv.TTopLevelId "+
                         "  ,case wd.WordClass when 'idiom' then 9 else wd.WordLevel end as WordLevel, WordCnt " +
                         "From ";

        string wordSql = "(Select "+
                         "LevelId, Word, WordClass, HeadWord, WordLevel, WordMean " +
                         ", (Select Count(*) From ANALYZE_NEW_WORD Z, ANALYZE_WORD_LEVEL T Where Z.LevelId = T.LevelId and Z.Word = A.Word and Z.WordClass = A.WordClass and Z.WordMean = A.WordMean and T.DelYn <> 'Y' ) as WordCnt  " +
                         "From ANALYZE_NEW_WORD A) wd";

        string LevelSql = "(Select " +
                          " (Select TopLevelId From ANALYZE_WORD_LEVEL where LevelId = A.TopLevelId) TTopLevelId," +
                          " TopLevelId, LevelId, LevelName, LevelSeq" +
                          " from ANALYZE_WORD_LEVEL A where len(LevelId) = 9 and DelYn <> 'Y' ) lv";


        string groupBy = " group by WordLevel order by WordLevel ASC ";


        listSql = selectData + " From (" + listSql + wordSql + ", " + LevelSql + where + ") T " + groupBy;

        // New Word
        SqlDataReader dr = _db.GetQueryResult(listSql);

        if (dr.HasRows) {
            while (dr.Read()) {
                nwStatis.Add(dr["WordLevel"].ToString(), dr["WordCount"].ToString());
                _nwTotalCount += Int32.Parse(dr["WordCount"].ToString());
            }
        }

        dr.Close();

        if (_nwTotalCount > 0) {
            for (int i = 1; i <= 9; i++) {
                string key = i.ToString();

                if (nwStatis.ContainsKey(key))
                {
                    int c = Int32.Parse(nwStatis[key].ToString());
                    double d = Math.Round((c/(double)_nwTotalCount * 100), 2);
                    nwStatis.Add("pc" + key, d);
                }
                else {
                    nwStatis.Add(key, "0");
                    nwStatis.Add("pc"+key, "0");
                }

            }
        }


        // New New Word
        string nonDupNewWordSql = "" +
            " WITH level_tmp as (                                                                                                            " +
            "  Select ROW_NUMBER() OVER(ORDER BY TopSeq + TTopSeq + LevelSeq) as rNum, LevelId, TopLevelId, TTopLevelId  From               " +
            "        (                                                                                                                      " +
            "        Select                                                                                                                 " +
            "          (Select LevelSeq * convert(int, '1' + replicate('0', 5 - len(LevelSeq))) From ANALYZE_WORD_LEVEL where LevelId = A.TopLevelId) TopSeq, " +
            "          (Select LevelSeq * convert(int, '1' + replicate('0', 7 - len(LevelSeq))) From ANALYZE_WORD_LEVEL where LevelId = (Select TopLevelId From ANALYZE_WORD_LEVEL where LevelId = A.TopLevelId)) TTopSeq, " +
            "          (Select TopLevelId From ANALYZE_WORD_LEVEL where LevelId = A.TopLevelId) TTopLevelId,                                 " +
            "          TopLevelId, LevelId, LevelName, LevelSeq                                                                              " +
            "        from ANALYZE_WORD_LEVEL A where len(LevelId) = 9 and DelYn <> 'Y'                                                       " +
            "        )Z                                                                                                                      " +
            " ),                                                                                                                             " +
            " word_tmp as (                                                                                                                  " +
            " Select *,                                                                                                                      " +
            "   (Select Count(*) From ANALYZE_NEW_WORD Z Where Z.LevelId = T.LevelId and Z.Word = T.Word and Z.WordClass = T.WordClass and T.WordMean = T.WordMean) as WordCnt                                                   " +
            " From (                                                                                                                         " +
            "    Select                                                                                                                      " +
            "      lv.LevelId, wd.Word, wd.WordClass, wd.WordMean, case wd.WordClass when 'idiom' then 9 else wd.WordLevel end as WordLevel ," +
            "      rNum as seq, TopLevelId, TTopLevelId                                                                                      " +
            "    From                                                                                                                        " +
            "      (Select                                                                                                                   " +
            "        LevelId, Word, WordClass, HeadWord, WordLevel, WordMean From ANALYZE_NEW_WORD) wd,                                      " +
            "      (Select                                                                                                                   " +
            "        rNum, TopLevelId, TTopLevelId, LevelId                                                                                  " +
            "        from level_tmp ) lv                                                                                                     " +
            "    Where                                                                                                                       " +
            "      lv.LevelId = wd.LevelId                                                                                                   " +
            "    ) T                                                                                                                         " +
            " ),                                                                                                                             " +
            " dup_word_tmp as (                                                                                                              " +
            "  Select * From (                                                                                                               " +
            "  Select                                                                                                                        " +
            "    WordLevel, LevelId, Word, WordClass, WordMean, WordCnt, TopLevelId, TTopLevelId,                                            " +
            "    case when (Select isnull(count(*), 0) from word_tmp tp where tp.Word = TA.word and tp.WordClass = TA.WordClass and tp.WordMean = TA.WordMean and tp.seq < TA.seq ) = 0 then 'N' else 'Y' end as dup " +
            "  from                                                                                                                         " +
            "    word_tmp TA                                                                                                                " +
            " ) A Where dup = 'N'                                                                                                           " +
            ")                                                                                                                              " +
            "Select count(Word) as WordCount, WordLevel from dup_word_tmp " + dWhere + " group by WordLevel order by WordLevel ASC";

        SqlDataReader dr2 = _db.GetQueryResult(nonDupNewWordSql);

        if (dr2.HasRows)
        {
            while (dr2.Read())
            {
                nnwStatis.Add(dr2["WordLevel"].ToString(), dr2["WordCount"].ToString());
                _nnwTotalCount += Int32.Parse(dr2["WordCount"].ToString());
            }
        }

        dr2.Close();

        if (_nnwTotalCount > 0)
        {
            for (int i = 1; i <= 9; i++)
            {
                string key = i.ToString();

                if (nnwStatis.ContainsKey(key))
                {
                    int c = Int32.Parse(nnwStatis[key].ToString());
                    double d = Math.Round((c / (double)_nnwTotalCount * 100), 2);
                    nnwStatis.Add("pc" + key, d);
                }
                else
                {
                    nnwStatis.Add(key, "0");
                    nnwStatis.Add("pc" + key, "0");
                }
            }
        }
    }

    private void makeBaseWhere() {
        where = " where lv.LevelId = wd.LevelId ";
        dWhere = " where 1 = 1";

        // Level 1
        if (!isEmpty(sv.searchLevel1) && sv.searchLevel1 != "0")
        {
            where += " and lv.TTopLevelId = '" + sv.searchLevel1 + "'";
            dWhere += "and TTopLevelId = '" + sv.searchLevel1 + "'";
        }
        // Level 2
        if (!isEmpty(sv.searchLevel2))
        {
            where += " and lv.TopLevelId = '" + sv.searchLevel2 + "'";
            dWhere += "and TopLevelId = '" + sv.searchLevel2 + "'";
        }
        // Level 3
        if (!isEmpty(sv.searchLevel3))
        {
            where += " and lv.LevelId = '" + sv.searchLevel3 + "'";
            dWhere += "and LevelId = '" + sv.searchLevel3 + "'";
        }

        if (!isEmpty(sv.wordCount1) || !isEmpty(sv.wordCount2) || !isEmpty(sv.wordCount3) || !isEmpty(sv.wordCount4) ) {
            where += " and ( ## ";
            dWhere += " and ( ## ";

            if (!isEmpty(sv.wordCount1))
            {
                where += " or WordCnt = 1";
                dWhere += " or WordCnt = 1"; 
            }

            if (!isEmpty(sv.wordCount2))
            {
                where += " or WordCnt = 2";
                dWhere += " or WordCnt = 2";
            }

            if (!isEmpty(sv.wordCount3))
            {
                where += " or WordCnt = 3";
                dWhere += " or WordCnt = 3";
            }

            if (!isEmpty(sv.wordCount4))
            {
                where += " or WordCnt > 3";
                dWhere += " or WordCnt > 3";
            }

            where = where.Replace("##  or", "") + ")";
            dWhere = dWhere.Replace("##  or", "") + ")";
        }
    }

    public string getLevel(object level)
    {
        string lv = level as string;
        string result = "";

        if (lv == "8")
        {
            result = "기타";
        }
        else {
            result = "Level " + lv;
        }

        return result;
    }

    protected void Page_Unload(object sender, EventArgs e)
    {
        _db.ReleaseConnection();
    }
}
